use multipart::server::nickel::nickel::hyper::client::pool;
use sqlx::sqlite::SqlitePool;

use sqlx::{Row, Sqlite};
use tauri::path::PathResolver;

const URL: &str = "sqlite:/home/hy/桌面/hy220/tauri-app/src-tauri/target/release/_up_/data/app.db";
// 初始化数据库
pub async fn init_db() -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;

    // 创建图片表
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS imgs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            size INTEGER NOT NULL,
            type TEXT NOT NULL,
            fbl TEXT NOT NULL,
            exif TEXT,

            -- 管理信息
            upload_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            is_deleted INTEGER DEFAULT 0          -- 软删除标记0=正常1=已删除
        )"
    )
    .execute(&pool)
    .await?;

    
    // println!("数据库初始化成功！");
    Ok(())
}

// 从相册删除相片
pub async fn remove_img_from_album(album_id: i64,image_id: i64) -> Result<(), sqlx::Error> {
    let pool=SqlitePool::connect(URL).await?;
    sqlx::query("DELETE FROM album_images WHERE album_id =?1 AND image_id =?2")
    .bind(album_id)
    .bind(image_id)
    .execute(&pool)
    .await?;
    Ok(())
}
//修改相册
pub async fn update_album(id: i64,name: &str,description: &str) -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    sqlx::query("UPDATE albums SET name = ?1, description = ?2 WHERE id = ?3")
    .bind(name) 
   .bind(description)
   .bind(id)
   .execute(&pool)
   .await?;
    Ok(())
}
// 删除相册
pub async fn delete_album(id: i64) -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    sqlx::query("UPDATE albums SET is_deleted = 1 WHERE id = ?1")
     .bind(id)
     .execute(&pool)
     .await?;   
    Ok(())
}


// 初始化相册图片表
pub async fn init_album_img() -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS album_images (
                album_id INTEGER NOT NULL,
                image_id INTEGER NOT NULL,
                -- 管理信息
                created_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
                updated_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
                is_deleted INTEGER NOT NULL DEFAULT 0,


                PRIMARY KEY (album_id, image_id),
                FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE CASCADE,
                FOREIGN KEY (image_id) REFERENCES imgs(id) ON DELETE CASCADE
            );"
    )
    .execute(&pool)
    .await?;
    Ok(())
}
// 添加图片到相册
pub async fn add_img_to_album(album_id: i64,image_id: i64) -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    sqlx::query("INSERT INTO album_images (album_id,image_id) VALUES (?1, ?2)")
        .bind(album_id)
        .bind(image_id)
       .execute(&pool)
       .await?;
    Ok(())
}
// 创建相册
pub async fn create_album() -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS albums (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,-- 相册名称
            description TEXT
            
            created_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            updated_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
            is_deleted INTEGER DEFAULT 0
            
        )"
    ).execute(&pool).await?;
    Ok(())
}

// 插入相册
pub async fn insert_album(name: &str,description: &str) -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    sqlx::query("INSERT INTO albums (name,description) VALUES (?1,?2)")
       .bind(name)
       .bind(description)
       .execute(&pool)
       .await?;
    println!("相册插入成功！{},{}", name, description);
    Ok(())
}

// 获取相册列表
pub async fn get_all_albums() -> Result<Vec<(i64,String,String,String)>, sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    let rows = sqlx::query("SELECT
     id,name,created_time,updated_time,is_deleted ,description
     FROM 
     albums
     WHERE is_deleted = 0")
      .fetch_all(&pool)
      .await?;
    let mut albums: Vec<(i64, String, String, String)>=Vec::new();
    for row in rows {
        let id: i64 = row.get("id");
        let name: String = row.get("name");
        let created_time: String = row.get("created_time");
        let description: String = row.get("description");
        albums.push((id,name,created_time,description));
    }
    Ok(albums)
}


// 获取相册里的图片
pub async fn get_album_imgs(id: i64) -> Result<Vec<(i64,String,String,i64,String,String,String)>, sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    let rows=sqlx::query("SELECT i.id, i.name, i.type, i.size, i.fbl, i.exif,i.upload_time
         FROM imgs i
         INNER JOIN album_images ai ON i.id = ai.image_id
         WHERE ai.album_id = ?1 AND i.is_deleted = 0"
        )
        .bind(id)
       .fetch_all(&pool)
       .await?;
    let mut imgs: Vec<(i64, String, String, i64, String, String,String)>=Vec::new();
    for row in rows {
        let id: i64 = row.get("id");
        let name: String = row.get("name");
        let type_: String = row.get("type");
        let size: i64 = row.get("size");
        let fbl: String = row.get("fbl");
        let exif: String = row.get("exif");
        let upload_time:String =row.get("upload_time");
        imgs.push((id,name,type_,size,fbl,exif,upload_time));
    }

    Ok(imgs)
}
// 插入图片信息
pub async fn insert_file(name: &str,size: i64,type_: &str,fbl: &str,exif: &str) -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    sqlx::query("INSERT INTO imgs (name,size,type,fbl,exif) VALUES (?1,?2,?3,?4,?5)")
       .bind(name)
       .bind(size)
       .bind(type_)
       .bind(fbl)
       .bind(exif)
       .execute(&pool)
       .await?;
    println!("图片插入成功！{}",name);
    Ok(())
}

// 删除图片信息
pub async fn delete_img(name: &str,id: i64) -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    sqlx::query("UPDATE imgs SET is_deleted = 1 WHERE id = ?1")
       .bind(id)
       .execute(&pool)
       .await?;
    println!("图片删除成功！{}", name);
    Ok(())
}
// 获取所有图片信息
pub async fn get_all_imgs() -> Result<Vec<(i64,String,String,i64,String,String,String)>, sqlx::Error> {
    let pool = SqlitePool::connect(URL).await?;
    let rows = sqlx::query("SELECT id,name,type,size,fbl,exif,upload_time FROM imgs
     WHERE is_deleted = 0")
       .fetch_all(&pool)
       .await?;
    let mut files: Vec<(i64, String, String, i64, String, String,String)> = Vec::new();
    for row in rows {
        let id: i64 = row.get("id");
        let name: String = row.get("name");
        let fbl: String = row.get("fbl");
        let type_: String = row.get("type");
        let size: i64 = row.get("size");
        let exif: String = row.get("exif");
        let upload_time:String =row.get("upload_time");
        files.push((id,name,type_,size,fbl,exif,upload_time));
    }
    Ok(files)
}
